<?php
//search.php
//This page contains the logic for the action of searching for information given some parameters.
        require_once("includes/overhead.php");

	require_once("includes/overhead.php");
	$pageTitle="Search Course Critique";
	$breadcrumbs[0]['URL']='index.php';
	$breadcrumbs[0]['Title']='Course Critique';
	$breadcrumbs[1]['URL']='Search.php';
	$breadcrumbs[1]['Title']='Search';
	$content="";
	$didYouKnow['Title']='News';
	$didYouKnow['Content']="CIOS Integration is up and running!	";
	//Initialize the variable that will hold all sections returned.
	$sections=null;
//If the user performs the search via the Search toolbar, it is considered "generic"
//and the system must determine if it is a professor or course.
$skip=0;
if (((isset($_REQUEST['Professor'])) || (isset($_REQUEST['generalSearch'])) || (isset($_REQUEST['Course'])) || (isset($_REQUEST['Department']) && isset($_REQUEST['CourseNumber']))))
{
	if (isset($_REQUEST['generalSearch'])) {
		if ($_REQUEST['generalSearch'] == "") {
			$skip=1;
			$genericSearchUnFiltered=mysql_real_escape_string($_REQUEST['generalSearch']);
		}else {
			$genericSearchUnFiltered=mysql_real_escape_string($_REQUEST['generalSearch']);
		}
	} 
	else if (isset($_REQUEST['Course'])) { $genericSearchUnFiltered=mysql_real_escape_string($_REQUEST['Course']); }
	else if (isset($_REQUEST['Professor']) && ($_REQUEST['Professor']!="Professor")) { $genericSearchUnFiltered=mysql_real_escape_string($_REQUEST['Professor']); }
	else if (isset($_REQUEST['Department']) && ($_REQUEST['Department']!="Department")) { $genericSearchUnFiltered=mysql_real_escape_string($_REQUEST['Department'])." ".mysql_real_escape_string($_REQUEST['CourseNumber']); }
	
	$genericSearch = trim(preg_replace('#[^\p{L}\p{N}||\'||\-]+#u', ' ', $genericSearchUnFiltered));
	$terms=1;
	$courseParts = null;

		for ($i=0;$i<strlen($genericSearch);$i++) {
			if (is_numeric(substr($genericSearch,$i,1))) {
				$courseParts[1]=substr($genericSearch,$i);
				$courseParts[0]=substr($genericSearch,0,$i);
				break;
			}
		}
	//$courseParts[0]=str_replace($courseParts[0]," ","");

	$d = new Department();
	$professorCount=0;
	$professors=null;
	if ($d->retrieveFromCode(mysql_real_escape_string($courseParts[0]))) { //Department found, so assume they are searching for a course.
		$getProfessorsQuery = mysql_query("select distinct ProfessorID as PID, CourseID as CID from Section join Course on Section.CourseID = Course.ID where DepartmentID=".$d->getID().(array_key_exists(1,$courseParts) ? " and Course.Number = ".mysql_real_escape_string($courseParts[1]) : " join Professor on Section.ProfessorID = Professor.ID order by Professor.LastName ASC,Professor.FirstName ASC"));
		while ($row = mysql_fetch_assoc($getProfessorsQuery)) {
			$p=new Professor();
			$p->retrieveFromID($row['PID']);
			$prof['P']=$p;
			$prof['Sections']=null;
			$gpa=0;
			$pa=0;
			$pb=0;
			$pc=0;
			$pd=0;
			$pf=0;
			$pw=0;
			$sectionCount=0;
			$totalStudents=0;
			$getSectionsQuery=mysql_query("select Section.ID as SID,GPA from Section join Course on Section.CourseID=Course.ID join Term on Section.TermID=Term.ID where ProfessorID=".$row['PID']." and CourseID = ".$row['CID']." order by Course.DepartmentID, Course.Number, Term.Year DESC, Term.Semester ASC, Section.Code ASC");
			while ($section = mysql_fetch_assoc($getSectionsQuery)) {
					$sectionCount++;
					$s = new Section();
					$s->retrieveFromID($section['SID']);
					$gpa+=$s->getGPA();
					$pa+=$s->getPercent_A();
					$pb+=$s->getPercent_B();
					$pc+=$s->getPercent_C();
					$pd+=$s->getPercent_D();
					$pf+=$s->getPercent_F();
					$pw+=$s->getPercent_W();
					$totalStudents+=$s->getSize();
					$prof['Sections'][] = $s;
			}
			$prof['GPA']=$gpa/$sectionCount;
			$prof['PctA']=$pa/$sectionCount;
			$prof['PctB']=$pb/$sectionCount;
			$prof['PctC']=$pc/$sectionCount;
			$prof['PctD']=$pd/$sectionCount;
			$prof['PctF']=$pf/$sectionCount;
			$prof['PctW']=$pw/$sectionCount;
			$prof['Size']=$totalStudents/$sectionCount;
			$professors[$professorCount++]=$prof;
		}
	} else {
    	$nameParts = explode(" ",$genericSearch);
		if (count($nameParts)>1) {
			$query=mysql_query("select Professor.ID as PID from Professor where LastName like '".mysql_real_escape_string($nameParts[0])."%' and FirstName like '%".mysql_real_escape_string($nameParts[1])."%' order by LastName ASC, FirstName ASC");
		} else {
		        $query =mysql_query("select Professor.ID as PID from Professor where LastName like '".mysql_real_escape_string($nameParts[0])."%' order by LastName ASC, FirstName ASC");
		}
		while ($row = mysql_fetch_assoc($query)) {
  			$p=new Professor();
			$p->retrieveFromID($row['PID']);
			$prof['P']=$p;
			$prof['Sections']=null;
			$gpa=0;
			$pa=0;
			$pb=0;
			$pc=0;
			$pd=0;
			$pf=0;
			$pw=0;
			$totalStudents=0;
			$sectionCount=0;
			$getSectionsQuery=mysql_query("select Section.ID as SID,GPA from Section join Course on Section.CourseID=Course.ID join Term on Section.TermID=Term.ID where ProfessorID=".$row['PID']." order by Course.DepartmentID, Course.Number, Term.Year DESC, Term.Semester ASC, Section.Code ASC");
			while ($section = mysql_fetch_assoc($getSectionsQuery)) {
					$sectionCount++;
					$s = new Section();
					$s->retrieveFromID($section['SID']);
					$gpa+=$s->getGPA();
					$pa+=$s->getPercent_A();
					$pb+=$s->getPercent_B();
					$pc+=$s->getPercent_C();
					$pd+=$s->getPercent_D();
					$pf+=$s->getPercent_F();
					$pw+=$s->getPercent_W();
					$totalStudents+=$s->getSize();
					$prof['Sections'][] = $s;
			}
			$prof['GPA']=$gpa/$sectionCount;
			$prof['PctA']=$pa/$sectionCount;
			$prof['PctB']=$pb/$sectionCount;
			$prof['PctC']=$pc/$sectionCount;
			$prof['PctD']=$pd/$sectionCount;
			$prof['PctF']=$pf/$sectionCount;
			$prof['PctW']=$pw/$sectionCount;
			$prof['Size']=$totalStudents/$sectionCount;
			$professors[$professorCount++]=$prof;
    	    }
	}
		

} else {
	$terms=0;
	$whereClause=null;
	//If the user chooses to search based on Professor Name
	//$queryString="select C.ID as CRN, concat(P.LastName, ', ',P.FirstName) as ProfName, C.Name as CourseName from Professor as P join Section as S on P.ID = S.ProfessorID join Course as C on S.CourseID=C.ID join Department as D on C.DepartmentID=D.ID"; 
	$queryString="select S.ID as SectionID from Professor as P join Section as S on P.ID = S.ProfessorID join Course as C on S.CourseID=C.ID join Department as D on C.DepartmentID=D.ID join Term as T on S.TermID=T.ID ";
	$s = new Section();
	if (isset($_REQUEST["Professor"]) && ($_REQUEST['Professor']!="Professor")) {
		$terms++;
		$fullName=mysql_real_escape_string($_REQUEST['Professor']);
		$nameParts=explode(", ",$fullName);
		$whereClause[]="P.LastName='".$nameParts[0]."' and P.FirstName='".$nameParts[1]."'";
	}
	
	if (isset($_REQUEST["Course"])) {
		$terms++;
		$fullCourseName=mysql_real_escape_string($_REQUEST['Course']);
		$courseParts=explode(" ",$fullCourseName);
		$dept = new Department();
		$dept->retrieveFromCode($courseParts[0]);
		$whereClause[]="D.ID=".$dept->getID()." and C.Number=".$courseParts[1];	
	}
	if (isset($_REQUEST["Term"])) {
		$terms++;
		$fullTermName=mysql_real_escape_string($_REQUEST['Term']);
		$TermParts=explode(" ",$fullTermName);
		$whereClause[]="T.Semester='".$TermParts[0]."' and YEAR(T.StartDate)=".$TermParts[1];
	}
	if (isset($_REQUEST['Department']) && ($_REQUEST['Department']!="Department")) {
		$terms++;
		$departmentCode=mysql_real_escape_string($_REQUEST['Department']);
		$dept = new Department();
		$dept->retrieveFromCode($departmentCode);
		$whereClause[]="D.ID=".$dept->getID();

	}
	if ($terms>0) {
		//Process search parameters
		if (count($whereClause)>0) {
			$queryString.=" where ".implode(" and ",$whereClause);
		}
		$queryString.=" order by Course.DepartmentID, Course.Number, Term.Year DESC, Term.Semester ASC, Section.Code ASC";
		//TODO: Develop sorting mechanism
		/*if (isset($_REQUEST['sort'])) {
			switch ($_REQUEST['sort']) {
				case 'GPA':
					$queryString.=" order by S.GPA DESC";
					break;
				case 'Professor':
					$queryString.=" order by P.LastName, P.FirstName";
					break;
				default:
					break;
			}
		}*/
		$query=mysql_query($queryString);	
		while ($row = mysql_fetch_assoc($query)) {
			$section = new Section();
			$section->retrieveFromID($row['SectionID']);
			$sections[] = $section;
		}
	}
}
		//If the user specifies a Data Format (html, xml, etc.) the system will find a way to display data in that format.
if (isset($_REQUEST['format'])) {
	$presentation = $_REQUEST['format'];
} else { $presentation = "html"; }

//Display different presentation types (to allow for different data access methods)	
if ($presentation=="xml") {
header ("content-type: text/xml");
	if ($terms>0) {
		$content="<searchResults>\n";
		for ($i=0;$i<count($professors);$i++) {
			$currentProf=$professors[$i];
			$content.="<Professor><ProfessorName>".$currentProf["P"]->getNeatName()."</ProfessorName>\n<AverageGPA>".sprintf("%3.2f",$currentProf['GPA'])."</AverageGPA>\n<AveragePercentA>".sprintf("%3.0f",$currentProf['PctA'])."</AveragePercentA>\n<AveragePercentB>".sprintf("%3.0f",$currentProf['PctB'])."</AveragePercentB>\n<AveragePercentC>".sprintf("%3.0f",$currentProf['PctC'])."</AveragePercentC>\n<AveragePercentD>".sprintf("%3.0f",$currentProf['PctD'])."</AveragePercentD>\n<AveragePercentF>".sprintf("%3.0f",$currentProf['PctF'])."</AveragePercentF>\n<AveragePercentW>".sprintf("%3.0f",$currentProf['PctW'])."</AveragePercentW>\n";
			for ($j=0;$j<count($currentProf['Sections']);$j++) {
				$currentSection=$currentProf['Sections'][$j];
				$c=$currentSection->getCourse();
				$d = $c->getDepartment();
				$content.="<Section>\n<Department>".$d->getCode()."</Department>\n<CourseNumber>".$c->getNumber()."</CourseNumber>\n<Term>".$currentSection->getTerm()->getSemester()."</Term>\n<Year>".$currentSection->getTerm()->getYear()."</Year>\n<SectionCode>".$currentSection->getCode()."</SectionCode>\n<Survey>".(($currentSection->getSurvey()!=1) ? "N" : "Y")."</Survey>\n<GPA>".sprintf("%3.2f",$currentSection->getGPA())."</GPA>\n<Size>".$currentSection->getSizeRange()."</Size>\n<PercentA>".$currentSection->getPercent_A()."</PercentA>\n<PercentB>".$currentSection->getPercent_B()."</PercentB>\n<PercentC>".$currentSection->getPercent_C()."</PercentC>\n<PercentD>".$currentSection->getPercent_D()."</PercentD>\n<PercentF>".$currentSection->getPercent_F()."</PercentF>\n<PercentW>".$currentSection->getPercent_W()."</PercentW>\n</Section>\n";			
			}
			$content.="</Professor>\n";
		}
            $content.="</searchResults>";
	}
	print $content;
}
else { //If the presentation type isn't one of the above just assume HTML.
	if ($terms>0) {
		$content="<h3>Grade distribution search results for \"".$genericSearch."\"</h3>
		<table border=1 style='font-size:12px'><tr><td style='width:250px;'>Professor</td><td>Survey</td><td style='width:20px;'>GPA</td><td>Size</td><td style='width:10px;'>A%</td><td style='width:10px;'>B%</td><td style='width:10px;'>C%</td><td style='width:10px;'>D%</td><td style='width:10px;'>F%</td><td style='width:10px;'>W%</td></tr>";
		for ($i=0;$i<count($professors);$i++) {
			$currentProf=$professors[$i];
			$content.="<tr style='background:#cccccc'><strong><td style='width:225px;'><strong>".$currentProf["P"]->getNeatName()."</strong> <small>(on average)</small></td><td></td><td style='width:20px;'><strong>".sprintf("%3.2f",$currentProf['GPA'])."</strong></td><td><strong> </strong></td><td style='width:10px;'><strong>".sprintf("%3.0f",$currentProf['PctA'])."</strong></td><td style='width:10px;'><strong>".sprintf("%3.0f",$currentProf['PctB'])."</strong></td><td style='width:10px;'><strong>".sprintf("%3.0f",$currentProf['PctC'])."</strong></td><td style='width:10px;'><strong>".sprintf("%3.0f",$currentProf['PctD'])."</strong></td><td style='width:10px;'><strong>".sprintf("%3.0f",$currentProf['PctF'])."</strong></td><td style='width:10px;'><strong>".sprintf("%3.0f",$currentProf['PctW'])."</strong></td></tr>";
			for ($j=0;$j<count($currentProf['Sections']);$j++) {
				$currentSection=$currentProf['Sections'][$j];
				$c=$currentSection->getCourse();
				$d = $c->getDepartment();
				$content.="<tr><td style='padding-left:30px;line-height:100%'>".$d->getCode()." ".$c->getNumber()." ".$currentSection->getTerm()->getSemester()." ".$currentSection->getTerm()->getYear()." (".$currentSection->getCode().")</td><td>".(($currentSection->getSurvey()!=1) ? " " : "<a href='https://gtwebapps.gatech.edu/cfeis/cios_new/stud_rpt.cfm?sect_code=".$currentSection->getCode()."&term_code=".$currentSection->getTerm()->getYear().$currentSection->getTerm()->getStartMonth()."-".$currentSection->getTerm()->getSemester()."&crse_num=".$currentSection->getCourse()->getNumber()."&subj_code=".$currentSection->getCourse()->getDepartment()->getCode()."' target='_blank'> View</a>")."</td><td>".sprintf("%3.2f",$currentSection->getGPA())."</td><td>".$currentSection->getSizeRange()."</td><td>".$currentSection->getPercent_A()."</td><td>".$currentSection->getPercent_B()."</td><td>".$currentSection->getPercent_C()."</td><td>".$currentSection->getPercent_D()."</td><td>".$currentSection->getPercent_F()."</td><td>".$currentSection->getPercent_W()."</td></tr>";
						
			}
		}
		$content.="<!--Success-->";
	} else { 
		$deptOptions="";
		$getDepartmentCodeListQuery=mysql_query("select Department.Code as Code from Department order by Department.Code");
		while ($row=mysql_fetch_assoc($getDepartmentCodeListQuery)) { $deptOptions.="<option>".$row['Code']."</option>"; }
		$profOptions="";
		$getProfListQuery=mysql_query("select Professor.LastName as LastName, Professor.FirstName as FirstName from Professor where (select count(*) from Section where ProfessorID=Professor.ID)>0 order by LastName, FirstName");
		while ($row=mysql_fetch_assoc($getProfListQuery)) { $profOptions.="<option>".$row['LastName'].", ".$row['FirstName']."</option>"; }
		$content="
		<h3>Search the Course Critique Database</h3>
		<p><form method='get' action='Search.php'><strong>Search by Course:</strong> <select name='Department'><option>Department</option>".$deptOptions."</select> <input type='text' name='CourseNumber' id='CourseNumber' value='Enter a Course Number' /> <input type='submit' value='Search' /></form><p><form><strong>Search by Professor: </strong><select name='Professor'><option>Professor</option>".$profOptions." <input type='submit' value='Search' /></form></p>
		";
	}
	include ("views/stdPage.php");
}

?>
